package vh.sphynx.runtime.util;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

import com.almworks.sqlite4java.SQLiteConnection;
import com.almworks.sqlite4java.SQLiteException;
import com.almworks.sqlite4java.SQLiteStatement;

public class SQLiteDB {
	
	SQLiteConnection connection;
	
	public SQLiteDB(String filename) throws SQLiteException {
		connection = new SQLiteConnection(new File(filename));
		connection.open(true);
	}
	
	public SQLiteConnection getConnection() {
		return connection;
	}
	
	public void close() {
		connection.dispose();
	}
	
	public List<String> getTables() throws SQLiteException {
		SQLiteStatement stmt = connection.prepare("select name from sqlite_master where type = 'table'", true);
		List<String> tables = new ArrayList<String>();
		while (stmt.step())
			tables.add(stmt.columnString(0));
		stmt.dispose();
		return tables;
	}
	
	public boolean isTableExists(String tablename) throws SQLiteException {
		SQLiteStatement stmt = connection.prepare("select name from sqlite_master where type = 'table' and name=?", true);
		stmt.bind(1, tablename);
		stmt.step();
		boolean result = stmt.hasRow();
		stmt.dispose();
		return result;
	}
	
	public void executeSql(String sql) throws SQLiteException {
		connection.exec(sql);
	}
	
	public void insert(String tableName, Object[] params) throws SQLiteException {
		StringBuffer sqlBuilder = new StringBuffer();
		sqlBuilder.append(String.format("insert into %s values (", tableName));
		for (int i = 0; i < params.length; i++) {
			if (i > 0) sqlBuilder.append(',');
			sqlBuilder.append('?');
		}
		sqlBuilder.append(')');
		SQLiteStatement stmt = connection.prepare(sqlBuilder.toString(), true);
		for (int i = 0; i < params.length; i++)
			bind(stmt, i+1, params[i]);
		stmt.stepThrough();
	}
	
	public void bind(SQLiteStatement stmt, int idx, Object value) throws SQLiteException {
		if (value instanceof String)
			stmt.bind(idx, (String)value);
		else if (value instanceof Integer)
			stmt.bind(idx, (Integer)value);
	}
}
